USE [NguyenHoa]
GO
-------------------------------------------------------------
--			@tblGroupPermission
-------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblGroupPermission_GetByUserId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblGroupPermission_GetByUserId]
GO

CREATE PROCEDURE [dbo].[_tblGroupPermission_GetByUserId]
(
	@UserId varchar(50)
)
AS
	SELECT P.*
	FROM tblUserGroup UG
			JOIN tblGroupPermission P ON UG.[GroupId] = P.[GroupId]
			JOIN tblFunction F ON P.[FunctionId] = F.[Id]
	WHERE UG.[Username] = @UserId AND (F.IsVisible =1 OR F.IsVisible IS NULL)
GO

-------------------------------------------------------------
--			@tblMainMenu
-------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblMainMenu_GetMainMenuByLanguage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblMainMenu_GetMainMenuByLanguage]
GO

CREATE PROCEDURE [dbo].[_tblMainMenu_GetMainMenuByLanguage]
(
	@LanguageId INT,
	@Type				char(1),
	@Username		varchar(50)
)
AS
 IF @Type = 'A'
 BEGIN
--	SELECT DISTINCT M.Id,M.Code, M.Link, M.Pages, ISNULL(L.[Name],M.[Name]) AS Title
--	FROM	tblUserGroup UG
--				INNER JOIN tblGroupPermission P ON UG.[GroupId] = P.[GroupId] AND UG.Username = @Username
--				INNER JOIN tblFunction F ON P.[FunctionId] = F.[Id] 
--				INNER JOIN tblSubMenu S ON (S.FunctionId = F.Id OR S.FunctionId = -1)
--				INNER JOIN tblMainMenu M ON S.MainMenuId = M.Id
--				LEFT JOIN tblMainMenuLocale L ON M.Id = L.MainMenuId AND L.LanguageId = @LanguageId
--	WHERE [Type] = @Type
	SELECT DISTINCT M.Id, M.Code, M.Link, M.Pages, ISNULL(L.[Name],M.[Name]) AS Title, M.[Order]
	FROM	tblMainMenu M 
				LEFT JOIN tblMainMenuLocale L ON M.Id = L.MainMenuId AND L.LanguageId = @LanguageId
	WHERE [Type] = @Type 
	ORDER BY M.[Order]
 END
 ELSE
 BEGIN
	SELECT DISTINCT M.Id, M.Code, M.Link, M.Pages, ISNULL(L.[Name],M.[Name]) AS Title, M.[Order]
	FROM	tblMainMenu M 
				LEFT JOIN tblMainMenuLocale L ON M.Id = L.MainMenuId AND L.LanguageId = @LanguageId
	WHERE [Type] = @Type 
	ORDER BY M.[Order]
 END
GO
-------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblSubMenu_GetSubMenuByLanguage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblSubMenu_GetSubMenuByLanguage]
GO

CREATE PROCEDURE [dbo].[_tblSubMenu_GetSubMenuByLanguage]
(
	@MainMenuId INT,
	@LanguageId INT,
	@Username		varchar(50)
)
AS
	DECLARE @SubMenuSource CHAR(1)
	SELECT @SubMenuSource = SubMenuSource FROM tblMainMenu WHERE Id = @MainMenuId
	IF (@Username IS NOT NULL)
	BEGIN
--		SELECT DISTINCT M.Id,M.Code, M.Link, ISNULL(L.[Name],M.[Name]) AS Title
--		FROM tblUserGroup UG
--			INNER JOIN tblGroupPermission P ON UG.[GroupId] = P.[GroupId] AND UG.Username = @Username
--			INNER JOIN tblFunction F ON P.[FunctionId] = F.[Id] 
--			INNER JOIN tblSubMenu M ON (M.FunctionId = F.Id OR M.FunctionId = -1)
--			LEFT JOIN tblSubMenuLocale L ON M.Id = L.SubMenuId AND L.LanguageId = @LanguageId
--		WHERE MainMenuId = @MainMenuId

		SELECT DISTINCT M.Id, M.Code, M.Link, ISNULL(L.[Name],M.[Name]) AS Title, '' as Source,M.[Order]
		FROM tblSubMenu M
				LEFT JOIN tblSubMenuLocale L ON M.Id = L.SubMenuId AND L.LanguageId = @LanguageId
		WHERE MainMenuId = @MainMenuId
		ORDER BY M.[Order]
	END
	ELSE
	BEGIN	
		IF(@SubMenuSource IS NULL OR @SubMenuSource = '')
		BEGIN
			SELECT DISTINCT M.Id, M.Code, M.Link, ISNULL(L.[Name],M.[Name]) AS Title, '' as Source,M.[Order]
			FROM tblSubMenu M
					LEFT JOIN tblSubMenuLocale L ON M.Id = L.SubMenuId AND L.LanguageId = @LanguageId
			WHERE MainMenuId = @MainMenuId
			ORDER BY M.[Order]
		END
		ELSE
		BEGIN
	
			IF(@SubMenuSource = 'P')
			BEGIN
				SELECT DISTINCT M.Id, M.Code, 'ProductDetail.aspx?id='+ Convert(char(10),M.Id) as Link, 
							ISNULL(L.[Name],M.[Name]) AS Title, @SubMenuSource as Source
				FROM tblProduct M
						LEFT JOIN tblProductLocale L ON M.Id = L.ProductId AND L.LanguageId = @LanguageId
			END

			IF (@SubMenuSource = 'N')
			BEGIN
				SELECT DISTINCT M.Id,M.Code,
							'NewsPage.aspx?t=' + ISNULL(M.[Type],'N')+'&tid=' + Convert(char(10),M.Id) as Link , 
							ISNULL(L.[Name],M.[Name]) AS Title, @SubMenuSource as Source
				FROM tblNewsCategory M
						LEFT JOIN tblNewsCategoryLocale L ON M.Id = L.NewsCategoryId AND L.LanguageId = @LanguageId
				WHERE (M.ParentId IS NULL) AND (M.[Type] = 'N')
			END
			IF (@SubMenuSource = 'K')
			BEGIN
				SELECT DISTINCT M.Id,M.Code,
							'PromotionsPage.aspx?t=' + ISNULL(M.[Type],'P')+'&tid=' + Convert(char(10),M.Id) as Link , 
							ISNULL(L.[Name],M.[Name]) AS Title, @SubMenuSource as Source
				FROM tblNewsCategory M
						LEFT JOIN tblNewsCategoryLocale L ON M.Id = L.NewsCategoryId AND L.LanguageId = @LanguageId
				WHERE (M.ParentId IS NULL) AND (M.[Type] = 'P')
			END

		END
	END
GO
-------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblSubMenu_GetSubMenuByParent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblSubMenu_GetSubMenuByParent]
GO

CREATE PROCEDURE [dbo].[_tblSubMenu_GetSubMenuByParent]
(
	@ParentId		INT,
	@LanguageId INT,
	@Source			CHAR(1)
)
AS
	IF @Source = 'P'
	BEGIN
		SELECT DISTINCT M.Id,M.Code, 'ProductDetail.aspx?id='+ Convert(char(10),M.Id) as Link, 
					ISNULL(L.[Name],M.[Name]) AS Title, @Source as Source
		FROM tblProduct M
				LEFT JOIN tblProductLocale L ON M.Id = L.ProductId AND L.LanguageId = @LanguageId
		WHERE @ParentId IS NULL
	END
	ELSE IF  @Source = 'N'
	BEGIN
		SELECT DISTINCT M.Id,M.Code,
				'NewsPage.aspx?t=' + ISNULL(M.[Type],'N')+'&tid = ' + Convert(char(10),M.Id) as Link , 
				ISNULL(L.[Name],M.[Name]) AS Title, @Source as Source
		FROM tblNewsCategory M
				LEFT JOIN tblNewsCategoryLocale L ON M.Id = L.NewsCategoryId AND L.LanguageId = @LanguageId
		WHERE M.ParentId = @ParentId
	END
	ELSE
	BEGIN

		SELECT DISTINCT M.Id,M.Code, M.Link, ISNULL(L.[Name],M.[Name]) AS Title,'' as Source,M.[Order]
		FROM tblSubMenu M LEFT JOIN tblSubMenuLocale L
					ON M.Id = L.SubMenuId AND L.LanguageId = @LanguageId
		WHERE ParentId = @ParentId
		ORDER BY M.[Order]
	END
GO

-------------------------------------------------------------
--			@tblUser
-------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblUser_GetByGroupId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblUser_GetByGroupId]
GO

CREATE PROCEDURE [dbo].[_tblUser_GetByGroupId]
(
	@GroupId INT
)
AS
	SELECT U.*, [UserGroupId] = UG.[Id]
	FROM tblUserGroup UG INNER JOIN tblUser U ON UG.[Username] = U.[Username]
	WHERE UG.[GroupId] = @GroupId
	ORDER BY U.[UserName], U.[Fullname]
GO
-------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblUser_GetUserNotInGroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblUser_GetUserNotInGroup]
GO

CREATE PROCEDURE [dbo].[_tblUser_GetUserNotInGroup]
(
	@GroupId BIGINT
)
AS
	SELECT U.*
	FROM tblUser U
	WHERE U.[Username] NOT IN (	SELECT [Username] 
															FROM tblUserGroup G 
															WHERE G.[GroupId] = @GroupId)
	ORDER BY U.[UserName], U.[FullName]
GO

-------------------------------------------------------------
--			@tblFunction
-------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblFunction_GetByGroupId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblFunction_GetByGroupId]
GO

CREATE PROCEDURE [dbo].[_tblFunction_GetByGroupId]
(
	@GroupId BIGINT
)
AS
	IF(@GroupId IS NOT NULL)
	BEGIN
		SELECT F.*, [GroupPermissionId] = G.[Id]
		FROM	tblFunction F	LEFT JOIN tblGroupPermission G 
					ON G.[FunctionId] = F.[Id] AND G.[GroupId] = @GroupId
		ORDER BY F.[Name]
	END
	ELSE
	BEGIN
		SELECT F.*, [GroupPermissionId] = NULL
		FROM tblFunction F
		ORDER BY F.[Name]
	END
GO

-------------------------------------------------------------
--			@tblFunction
-------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblFunction_GetVisibleFunction]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblFunction_GetVisibleFunction]
GO

CREATE PROCEDURE [dbo].[_tblFunction_GetVisibleFunction]

AS
	SELECT * FROM tblFunction WHERE IsVisible IS NULL OR IsVisible = 1
GO

--------------------------------------------------------------------
--						@tblSetting
--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblSetting_IncreasePageview]') and type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblSetting_IncreasePageview]
GO

CREATE PROCEDURE [dbo].[_tblSetting_IncreasePageview]
AS
	UPDATE tblSetting
	SET [Value] = CAST((CAST(ISNULL([Value],'0') AS INT) + 1) AS NVARCHAR(512))
	WHERE [Name] = 'PAGE_VIEW'
GO

--------------------------------------------------------------------
--						@tblContactInfo
--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblContactInfo_GetByTypeAndLanguage]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblContactInfo_GetByTypeAndLanguage]
GO

CREATE PROCEDURE [dbo].[_tblContactInfo_GetByTypeAndLanguage]
(
	@Type				CHAR(2),
	@LanguageId INT,
	@IsShow			BIT
)
AS
	SELECT *
	FROM tblContactInfo
	WHERE (@Type IS NULL OR @Type = [Type]) AND
				(@LanguageId IS NULL OR @LanguageId = [LanguageId]) AND
				(@IsShow IS NULL OR @IsShow = [IsShow]) 
GO
